clear all
set more off, perm
set maxvar 120000
set varabbrev off
* ---------------------------------------------- *
global dir 	"`1'"
global Data 	$dir/Data
global Tables 	$dir/Tables
global Figures 	$dir/Figures
global Work 	$dir/Work
global Temp 	$dir/Temp
global Pseudo	$dir/Work/Temp
* ---------------------------------------------- *
* [1] LOAD QUARTERLY COMPUSTAT: wrds library: /wrdslin/comp/sasdata/d_na/
import sas using $Data/Compustat/fundq.sas7bdat, case(lower) clear 
keep if index(curcdq, "USD")>0
* *********************************
* compute value of preferred stock
* pstk:   carrying value 
* In addition to pstk, Ken also uses data on liquidating value and redemption values --> liquidating value is not in the quarterly datadaset
generate pfd=pstkq
replace  pfd=0 if pfd==.

* value of common equity
generate steq=seqq				/* Stockholders Equity */
replace  steq=ceqq+pfd		if steq==.	/* Common/Ordinary Equity + Preferred  */
replace  steq=atq-ltq		if steq==.	/* assets minus liabilities */

*txditcq: deferred taxes and investment tax credit (balance sheet)
*txdbq  : deferred taxes (balance sheet)
generate dfd_taxes=txditcq
replace  dfd_taxes=txdbq 	if dfd_taxes==.
replace  dfd_taxes=0		if dfd_taxes==.

* Ken writes on his website: "Because of changes in the treatment of deferred taxes described in FASB 109, files produced after August 2016 no longer add Deferred Taxes and Investment 
* Tax Credit to BE for fiscal years ending in 1993 or later."
replace dfd_taxes=0 if fyearq>=1993

gen bookqtly=steq + dfd_taxes -pfd

generate shsout=cshprq*ajexq
keep conm* gvkey cshoq cshprq shsout ajexq adjex datadate datacqtr datafqtr fyr fyearq fqtr ibq niq xidoq spiq nrtxtq bookqtly dvpq saleq
tempfile tmp
save "`tmp'", replace
/* ********************************************************************* */
import sas using $Data/CRSP/ccmxpf_linktable.sas7bdat, case(lower) clear
keep if  index(linktype,"L") 
keep if (index(linkprim,"P") |  index(linkprim,"C"))
format linkdt linkenddt %td

joinby gvkey using "`tmp'"
keep if datadate>=linkdt & datadate<=linkenddt
rename lpermno permno
rename lpermco permco
drop linkdt linkenddt
* ******************************
* will need to tsset the data to compute moving average of earnings
drop if fqtr==.
* get rid of duplicate obs 
*linktype: "LD"  Duplicate link to a security. Another GVKEY/IID is a better link to that CRSP 
duplicates tag permco permno datadate, gen(mf)
tabulate mf

drop if mf>0 & index(linktype,"LD")>0
drop mf
* ****** 
*linkprim: Primary issue marker for the link.
duplicates tag permco permno datadate, gen(mf)
tabulate mf
drop if mf>0 & index(linkprim,"P")==0
drop mf
/* ********************************************************************* */
* compute earnings before extraordinary items
egen tmp=rowtotal(xidoq spiq nrtxtq)
generate street=niq - tmp
replace  street=ibq		if street==.
replace  street=niq  		if street==.

* may have multiple records when firms change fiscal years
egen id=group(permno fyr)
generate qd=yq(fyearq,fqtr)
format qd %tq
drop if qd==.
tsset id qd

gen streetLTM=street+L1.street+L2.street+L3.street  
gen saleLTM  =saleq +L1.saleq +L2.saleq +L3.saleq 
/* ********************************************************************* */
* 1,080 obs are duplicates permco/permno/datadate --> caused by changes in fiscal years
duplicates tag permco permno datadate, gen(mf)
tabulate mf
* changes in FYs --> cause streetLTM==.
drop if mf==1 & streetLTM==.
drop mf
* ***********
duplicates tag permco permno datadate, gen(mf)
tabulate mf
* 34 observations left with duplicates --> take mean 
collapse (mean) bookqtly streetLTM saleLTM ibq niq dvpq shsout cshprq ajexq  (lastnm) fyearq fqtr conm, by(permco permno datadate)
merge 1:1 permco permno datadate using  $Data/read_compustat, keepusing(permco permno conm datadate bookeq sale street)
keep if year(datadate)>1961
drop _merge
* ********************************
preserve
keep permco permno conm datadate streetLTM saleLTM shsout cshprq ajexq street sale  

foreach var in street sale  {
replace `var'LTM=`var' if `var'LTM==.
drop `var'
}
gen datem=mofd(datadate)
format datem %tm

tsset permno datem
tsfill
foreach var of varlist permco datadate {
	replace `var'=L.`var' if `var'==.
}
sort permno datem
bys permno: replace conm=conm[_n-1] if permno==L.permno

* interpolate data within a quarter -- will use as proxies for "news"
foreach var in street sale {
	bys permno: ipolate `var'LTM datem, gen(i`var')
	replace `var'LTM=i`var' if `var'LTM==. & datem-mofd(datadate)<3
	drop i`var'
	}
* don't change the number of shares and crsp adjustment factor within a quarter
foreach var in shsout cshprq ajexq {
	replace `var'=L.`var' if `var'==. & datem-mofd(datadate)<3
} 
keep permco permno datem conm streetLTM saleLTM shsout cshprq ajexq
save $Work/street_mthly, replace
restore
* ********************************
keep permno permco conm datadate bookqtly bookeq
sort permco permno datadate
format datadate %td

gen datem=mofd(datadate)
format datem %tm

tsset permno datem
tsfill
sort permno datem 
bys permno: replace conm=conm[_n-1] if length(conm)==0 & permno==L.permno

foreach var of varlist permco datadate {
	replace `var'=L.`var' if missing(`var') 
}
* don't update quarterly book values between reporting quarters. 
foreach var of varlist bookqtly  {
	replace `var'=L1.`var' if missing(`var') & (datem-mofd(datadate)<=2)
}
foreach var of varlist bookeq  {
	replace `var'=L1.`var' if missing(`var') & (datem-mofd(datadate)<=11)
}
keep permco permno conm datadate datem book*
gen mdatadate=mofd(datadate)
format mdatadate %tm

save $Work/beme, replace
* ********************************
* add market cap for beme ratio
* ********************************
import sas $Data/CRSP/stocknames.sas7bdat, clear case(lower)
keep permno permco comnam namedt nameenddt siccd shrcd exchcd 
sort permco permno nameenddt

* find end-of-the-month day for the last record of each stock
by permco permno:  gen obs=_n
by permco permno:  gen tot=_N
replace nameenddt=mdy(12,31,year(nameenddt)) 			if month(nameenddt)==12 & obs==tot
replace nameenddt=mdy(month(nameenddt)+1,1,year(nameenddt))-1 	if month(nameenddt)<=11 & obs==tot
drop obs tot

tempfile header
save "`header'", replace
/* ********************************************************************* */
import sas $Data/CRSP/msf.sas7bdat, clear case(lower)
keep permno permco date prc shrout ret retx cfa* 
keep if year(date)>1961

joinby permco permno using "`header'"
keep if date>=namedt & date<=nameenddt
keep if shrcd==10 | shrcd==11
/* *********************** */
gen mcap=abs(prc)*shrout 
* compute market cap for permcos with multiple permnos; keep permno for the security with largest market cap
sort permco date mcap
collapse (sum) mcap (lastnm) comnam shrout cfacshr permno, by(permco date)

gen mdatadate=mofd(date)
format mdatadate %tm

rename date crspdate
format crspdate %td
***************************
merge 1:m permco permno mdatadate using $Work/beme 
keep if _merge==3
drop mdatadate _merge
***************************
* use annual book when quarterly data is missing
generate  bookLTM=bookqtly
replace   bookLTM=bookeq   if missing(bookLTM)

generate  bemeLTM=1000*bookLTM/mcap
keep co* permco permno crspdate datadate datem book* beme* shrout cfa* 
***************************
* Following Ken French, assume it takes (at least) 6 months for the market to learn bookeq
* Also, following KF, line up the timing of book equity and market cap
replace datem=datem+6
format datem %tm
order co* permco permno datem crspdate datadate book* beme*

save $Work/bemeLTM, replace
***************************
*!find $Temp -type f -delete
